{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c9c8cbf4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "#import glob\n",
    "import tqdm\n",
    "import random\n",
    "import numpy as np\n",
    "from sklearn.model_selection import train_test_split\n",
    "import time,datetime\n",
    "import variable_bin_methods as varbin_meth\n",
    "import variable_encode as var_encode\n",
    "from sklearn.model_selection import GridSearchCV\n",
    "from sklearn.metrics import roc_curve, auc,confusion_matrix,recall_score,precision_score,accuracy_score\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from imblearn.under_sampling import RandomUnderSampler\n",
    "from imblearn.over_sampling import SMOTE\n",
    "from imblearn.over_sampling import BorderlineSMOTE\n",
    "import missingno as msno\n",
    "import matplotlib\n",
    "#matplotlib.use(arg='Qt5Agg')\n",
    "import matplotlib.pyplot as plt\n",
    "matplotlib.rcParams['font.sans-serif']=['SimHei']   \n",
    "matplotlib.rcParams['axes.unicode_minus']=False  \n",
    "from statsmodels.stats.outliers_influence import variance_inflation_factor\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\") ##忽略警告\n",
    "import variable_bin_methods as vbm\n",
    "import pickle\n",
    "import copy\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b76f55ad",
   "metadata": {},
   "source": [
    "# 读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "918d3a6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_10 = pd.read_excel('最终2018年改变分箱最后19个特征数据的分箱.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1306bff7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_11 = pd.read_excel('最终2019年-1改变分箱最后19个特征数据的分箱.xlsx')\n",
    "df_12 = pd.read_excel('最终2019年-2改变分箱最后19个特征数据的分箱.xlsx')\n",
    "df_13 = pd.read_excel('最终2019年-3改变分箱最后19个特征数据的分箱.xlsx')\n",
    "df_14 = pd.read_excel('最终2019年-4改变分箱最后19个特征数据的分箱.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6de53159",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_X = df_10.drop(columns = ['loan_status'])\n",
    "data_y = df_10['loan_status']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bfd7e6b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_X_1 = df_11.drop(columns = ['loan_status'])\n",
    "data_y_1 = df_11['loan_status']\n",
    "data_X_2= df_12.drop(columns = ['loan_status'])\n",
    "data_y_2 = df_12['loan_status']\n",
    "data_X_3 = df_13.drop(columns = ['loan_status'])\n",
    "data_y_3 = df_13['loan_status']\n",
    "data_X_4 = df_14.drop(columns = ['loan_status'])\n",
    "data_y_4 = df_14['loan_status']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "cfeabf42",
   "metadata": {},
   "outputs": [],
   "source": [
    "## 读取上一步存的分箱规则和woe规则\n",
    "continuous_var_bin_read = open('continuous_var_bin.pkl','rb')\n",
    "continuous_var_bin_dict = pickle.load(continuous_var_bin_read)\n",
    "continuous_var_bin_read.close()\n",
    "\n",
    "categorical_var_bin_read = open('categorical_var_bin.pkl','rb')\n",
    "categorical_var_bin_dict = pickle.load(categorical_var_bin_read)\n",
    "categorical_var_bin_read.close()\n",
    "\n",
    "woe_list_read = open('woe_list.pkl','rb')\n",
    "woe_list_dict = pickle.load(woe_list_read)\n",
    "woe_list_read.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7498e474",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|█████████████████████████████████████████████████████████████████████████████████| 19/19 [00:00<00:00, 759.85it/s]\n"
     ]
    }
   ],
   "source": [
    "data_X_woe_1 = pd.DataFrame()\n",
    "for col_x in tqdm.tqdm([x + '_BIN' for x in data_X_1.columns]):\n",
    "    guize = woe_list_dict.get(col_x)\n",
    "    data_X_woe_1[col_x[:-4]] = data_X_1[col_x[:-4]].replace(list(guize.index),list(guize.values))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ed3b2b85",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([data_X_woe_1,data_y_1],axis = 1).to_excel('最终2019年-1改变分箱最后19个特征数据的入模测试集.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d22fcb0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3dce7fff",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|█████████████████████████████████████████████████████████████████████████████████| 19/19 [00:00<00:00, 655.07it/s]\n"
     ]
    }
   ],
   "source": [
    "data_X_woe_2 = pd.DataFrame()\n",
    "for col_x in tqdm.tqdm([x + '_BIN' for x in data_X_2.columns]):\n",
    "    guize = woe_list_dict.get(col_x)\n",
    "    data_X_woe_2[col_x[:-4]] = data_X_2[col_x[:-4]].replace(list(guize.index),list(guize.values))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "0e541b45",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([data_X_woe_2,data_y_2],axis = 1).to_excel('最终2019年-2改变分箱最后19个特征数据的入模测试集.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ecced2a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9d00bd43",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|█████████████████████████████████████████████████████████████████████████████████| 19/19 [00:00<00:00, 703.58it/s]\n"
     ]
    }
   ],
   "source": [
    "data_X_woe_3 = pd.DataFrame()\n",
    "for col_x in tqdm.tqdm([x + '_BIN' for x in data_X_3.columns]):\n",
    "    guize = woe_list_dict.get(col_x)\n",
    "    data_X_woe_3[col_x[:-4]] = data_X_3[col_x[:-4]].replace(list(guize.index),list(guize.values))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a8de28fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([data_X_woe_3,data_y_3],axis = 1).to_excel('最终2019年-3改变分箱最后19个特征数据的入模测试集.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1da8b45",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f552abd5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|█████████████████████████████████████████████████████████████████████████████████| 19/19 [00:00<00:00, 759.82it/s]\n"
     ]
    }
   ],
   "source": [
    "data_X_woe_4 = pd.DataFrame()\n",
    "for col_x in tqdm.tqdm([x + '_BIN' for x in data_X_4.columns]):\n",
    "    guize = woe_list_dict.get(col_x)\n",
    "    data_X_woe_4[col_x[:-4]] = data_X_4[col_x[:-4]].replace(list(guize.index),list(guize.values))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "bec5e702",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([data_X_woe_4,data_y_4],axis = 1).to_excel('最终2019年-4改变分箱最后19个特征数据的入模测试集.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2487b3a2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
